using System;
using System.Collections;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Configuration;
using System.Threading;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;
using System.Security.Cryptography;

namespace Data_Transfer_App
{
	/// <summary>
	/// Summary description for Form1.
	/// </summary>
	public class Main : System.Windows.Forms.Form
    {
        internal System.Windows.Forms.TextBox txtServer;
        internal System.Windows.Forms.TextBox txtDataBase;
		internal System.Windows.Forms.TextBox txtTable;
		internal System.Windows.Forms.TextBox txtWhere;
		internal System.Data.SqlClient.SqlConnection sqlConnection1;
		internal System.Windows.Forms.Button btexecute;
        internal System.Windows.Forms.ProgressBar progressBar1;
		/// <summary>
		/// Required designer variable.
		/// </summary>
        internal System.ComponentModel.Container components = null;
        internal RichTextBox txtLog;
        internal string TargetTable = string.Empty;
        private GroupBox groupBox1;
        private GroupBox groupBox2;
        internal TextBox txtServerDest;
        internal TextBox txtDataBaseDest;
        private Label label1;
        private Label label2;
        private GroupBox groupBox3;
    
        internal delegate void RunProcess();
        internal delegate void LogDelegate(string s);
        internal delegate void oDelegate();
        DataSet MasterTable = new DataSet();
        private CheckBox chkSecd;
        

        
        

		public Main()
		{
			//
			// Required for Windows Form Designer support
			//
            
			InitializeComponent();
            
			//
			// TODO: Add any constructor code after InitializeComponent call
			//
		}

		/// <summary>
		/// Clean up any resources being used.
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if (components != null) 
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		internal void InitializeComponent()
		{
            this.txtServer = new System.Windows.Forms.TextBox();
            this.txtDataBase = new System.Windows.Forms.TextBox();
            this.txtTable = new System.Windows.Forms.TextBox();
            this.txtWhere = new System.Windows.Forms.TextBox();
            this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
            this.btexecute = new System.Windows.Forms.Button();
            this.progressBar1 = new System.Windows.Forms.ProgressBar();
            this.txtLog = new System.Windows.Forms.RichTextBox();
            this.groupBox1 = new System.Windows.Forms.GroupBox();
            this.groupBox2 = new System.Windows.Forms.GroupBox();
            this.txtServerDest = new System.Windows.Forms.TextBox();
            this.txtDataBaseDest = new System.Windows.Forms.TextBox();
            this.label1 = new System.Windows.Forms.Label();
            this.label2 = new System.Windows.Forms.Label();
            this.groupBox3 = new System.Windows.Forms.GroupBox();
            this.chkSecd = new System.Windows.Forms.CheckBox();
            this.groupBox1.SuspendLayout();
            this.groupBox2.SuspendLayout();
            this.groupBox3.SuspendLayout();
            this.SuspendLayout();
            // 
            // txtServer
            // 
            this.txtServer.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
            this.txtServer.Location = new System.Drawing.Point(6, 19);
            this.txtServer.Name = "txtServer";
            this.txtServer.Size = new System.Drawing.Size(229, 20);
            this.txtServer.TabIndex = 0;
            this.txtServer.Text = "Server";
            // 
            // txtDataBase
            // 
            this.txtDataBase.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
            this.txtDataBase.Location = new System.Drawing.Point(5, 45);
            this.txtDataBase.Name = "txtDataBase";
            this.txtDataBase.Size = new System.Drawing.Size(229, 20);
            this.txtDataBase.TabIndex = 1;
            this.txtDataBase.Text = "Database";
            // 
            // txtTable
            // 
            this.txtTable.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
            this.txtTable.Font = new System.Drawing.Font("Microsoft Sans Serif", 10F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.txtTable.Location = new System.Drawing.Point(124, 116);
            this.txtTable.Name = "txtTable";
            this.txtTable.Size = new System.Drawing.Size(296, 23);
            this.txtTable.TabIndex = 4;
            this.txtTable.Text = "TableName";
            // 
            // txtWhere
            // 
            this.txtWhere.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
            this.txtWhere.Font = new System.Drawing.Font("Microsoft Sans Serif", 10F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.txtWhere.Location = new System.Drawing.Point(124, 142);
            this.txtWhere.Name = "txtWhere";
            this.txtWhere.Size = new System.Drawing.Size(426, 23);
            this.txtWhere.TabIndex = 5;
            this.txtWhere.Text = "where Table.rowid =\'\'";
            // 
            // sqlConnection1
            // 
            this.sqlConnection1.ConnectionString = "workstation id=IMPACT;packet size=4096;integrated security=SSPI;data source=unisy" +
                "ssql01;persist security info=False;initial catalog=plandata";
            this.sqlConnection1.FireInfoMessageEventOnUserErrors = false;
            // 
            // btexecute
            // 
            this.btexecute.FlatStyle = System.Windows.Forms.FlatStyle.Flat;
            this.btexecute.Location = new System.Drawing.Point(8, 459);
            this.btexecute.Name = "btexecute";
            this.btexecute.Size = new System.Drawing.Size(75, 23);
            this.btexecute.TabIndex = 6;
            this.btexecute.Text = "Execute";
            this.btexecute.Click += new System.EventHandler(this.btexecute_Click);
            // 
            // progressBar1
            // 
            this.progressBar1.Location = new System.Drawing.Point(8, 186);
            this.progressBar1.Name = "progressBar1";
            this.progressBar1.Size = new System.Drawing.Size(548, 16);
            this.progressBar1.TabIndex = 8;
            // 
            // txtLog
            // 
            this.txtLog.Location = new System.Drawing.Point(8, 208);
            this.txtLog.Name = "txtLog";
            this.txtLog.Size = new System.Drawing.Size(548, 245);
            this.txtLog.TabIndex = 10;
            this.txtLog.Text = "";
            // 
            // groupBox1
            // 
            this.groupBox1.Controls.Add(this.txtDataBase);
            this.groupBox1.Controls.Add(this.txtServer);
            this.groupBox1.Location = new System.Drawing.Point(12, 11);
            this.groupBox1.Name = "groupBox1";
            this.groupBox1.Size = new System.Drawing.Size(257, 83);
            this.groupBox1.TabIndex = 11;
            this.groupBox1.TabStop = false;
            this.groupBox1.Text = "Source Details";
            // 
            // groupBox2
            // 
            this.groupBox2.Controls.Add(this.txtServerDest);
            this.groupBox2.Controls.Add(this.txtDataBaseDest);
            this.groupBox2.Location = new System.Drawing.Point(275, 11);
            this.groupBox2.Name = "groupBox2";
            this.groupBox2.Size = new System.Drawing.Size(281, 83);
            this.groupBox2.TabIndex = 12;
            this.groupBox2.TabStop = false;
            this.groupBox2.Text = "Destination Details";
            // 
            // txtServerDest
            // 
            this.txtServerDest.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
            this.txtServerDest.Location = new System.Drawing.Point(6, 19);
            this.txtServerDest.Name = "txtServerDest";
            this.txtServerDest.Size = new System.Drawing.Size(269, 20);
            this.txtServerDest.TabIndex = 2;
            this.txtServerDest.Text = "Server";
            // 
            // txtDataBaseDest
            // 
            this.txtDataBaseDest.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
            this.txtDataBaseDest.Location = new System.Drawing.Point(6, 45);
            this.txtDataBaseDest.Name = "txtDataBaseDest";
            this.txtDataBaseDest.Size = new System.Drawing.Size(269, 20);
            this.txtDataBaseDest.TabIndex = 3;
            this.txtDataBaseDest.Text = "Database";
            // 
            // label1
            // 
            this.label1.AutoSize = true;
            this.label1.Location = new System.Drawing.Point(6, 25);
            this.label1.Name = "label1";
            this.label1.Size = new System.Drawing.Size(71, 13);
            this.label1.TabIndex = 13;
            this.label1.Text = "Source Table";
            // 
            // label2
            // 
            this.label2.AutoSize = true;
            this.label2.Location = new System.Drawing.Point(6, 51);
            this.label2.Name = "label2";
            this.label2.Size = new System.Drawing.Size(74, 13);
            this.label2.TabIndex = 14;
            this.label2.Text = "Row selection";
            // 
            // groupBox3
            // 
            this.groupBox3.Controls.Add(this.chkSecd);
            this.groupBox3.Controls.Add(this.label1);
            this.groupBox3.Controls.Add(this.label2);
            this.groupBox3.Location = new System.Drawing.Point(8, 101);
            this.groupBox3.Name = "groupBox3";
            this.groupBox3.Size = new System.Drawing.Size(548, 79);
            this.groupBox3.TabIndex = 15;
            this.groupBox3.TabStop = false;
            this.groupBox3.Text = "Table Details";
            // 
            // chkSecd
            // 
            this.chkSecd.AutoSize = true;
            this.chkSecd.Checked = true;
            this.chkSecd.CheckState = System.Windows.Forms.CheckState.Checked;
            this.chkSecd.Location = new System.Drawing.Point(418, 18);
            this.chkSecd.Name = "chkSecd";
            this.chkSecd.Size = new System.Drawing.Size(112, 17);
            this.chkSecd.TabIndex = 15;
            this.chkSecd.Text = "Secondary Tables";
            this.chkSecd.UseVisualStyleBackColor = true;
            // 
            // Main
            // 
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
            this.ClientSize = new System.Drawing.Size(568, 485);
            this.Controls.Add(this.groupBox2);
            this.Controls.Add(this.txtLog);
            this.Controls.Add(this.progressBar1);
            this.Controls.Add(this.btexecute);
            this.Controls.Add(this.txtWhere);
            this.Controls.Add(this.txtTable);
            this.Controls.Add(this.groupBox1);
            this.Controls.Add(this.groupBox3);
            this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedToolWindow;
            this.Name = "Main";
            this.Text = "Data Sync";
            this.groupBox1.ResumeLayout(false);
            this.groupBox1.PerformLayout();
            this.groupBox2.ResumeLayout(false);
            this.groupBox2.PerformLayout();
            this.groupBox3.ResumeLayout(false);
            this.groupBox3.PerformLayout();
            this.ResumeLayout(false);
            this.PerformLayout();

		}
		#endregion

        
        internal void btexecute_Click(object sender, System.EventArgs e)
        {
            
            allQueries.Clear();
            MasterTable.Clear();
            AllKeys.Clear();

            txtLog.Clear();
            progressBar1.Value = 0;
            if (!TestConnection()) return;
           
            progressBar1.Step = 1;
            
            settext("Data Sync uploading in progress");

            try
            {
                
                    RunProcess rp = new RunProcess(download);
                    rp.BeginInvoke(new AsyncCallback(upload), null);
            }
            catch (Exception ex)
            {   
                MessageBox.Show(ex.Message);
            }
            finally
            {
              
            }


        }
		
		
        
		internal void download()
		{
            WriteLog("Data Sync Downloading in progress");
            TargetTable = txtTable.Text.Trim();

            DataTable ds = ExecuteQuery("select * from " + TargetTable + " " + txtWhere.Text, TargetTable);
            MasterTable.Tables.Add(ds);

            WriteLog("Please wait while dependencies are determined.");

            string Query = "sp_MStablerefs N'dbo." + TargetTable + "', N'actualtables', N'both', null ";
            DataTable dsref = ReorderKeys(ExecuteQuery(Query, "ref"), TargetTable);
            AddKeys(dsref,TargetTable);
            SetProgressMax(dsref.Rows.Count);

            if(chkSecd.Checked)
            foreach (DataRow key in dsref.Rows)
            {
                TargetTable = key["FK_TABLE"].ToString().Trim();
                Query = "sp_MStablerefs N'dbo." + TargetTable + "', N'actualtables', N'both', null ";
                AddKeys(ReorderKeys(ExecuteQuery(Query, "ref"), TargetTable), TargetTable);
                PerformStep();
            }

            UpdateProgressbar(0);
            SetProgressMax(AllKeys.Count);

            TargetTable = txtTable.Text.Trim();
            foreach (KeyValuePair<string, Combi> key in AllKeys)
            {
                if (key.Value.Table.ToUpper() == txtTable.Text.Trim().ToUpper())
                {
                    PerformStep();
                    WriteData(key.Value.dr, txtWhere.Text.ToLower().Replace("where",""), true, null);
                }
                else
                {
                    PerformStep();
                    fetchSecondLevelData(key.Value);
                }
            }
            
            
		}

        

        internal void WriteData(DataRow dr, string Where, bool Join,string Table)
        {
            try
            {

                string primaryTable = dr["pk_table"].ToString().Trim().ToUpper();
                string RefTable = dr["fk_table"].ToString().Trim().ToUpper();
                string fetchQuery = "";
                string restofsql = Where;
                DataSet OldData = new DataSet();

                if (primaryTable == RefTable) return;

                if (Join)
                {
                    string SQl = createclause(primaryTable, RefTable, dr);
                    fetchQuery = "select " + RefTable + ".* from " + RefTable

                        + " join " + primaryTable + " on " + SQl + " and " + restofsql;

                }
                if (!Join)
                {
                    RefTable = Table;
                    fetchQuery = Where;
                }

                DataTable dsnew = ExecuteQuery(fetchQuery, RefTable);
                if (dsnew == null) return;

                if (!MasterTable.Tables.Contains(RefTable))
                {
                    MasterTable.Tables.Add(dsnew);
                }
                else
                foreach (DataRow d in dsnew.Rows)
                {
                    MasterTable.Tables[dsnew.TableName].LoadDataRow(d.ItemArray, false);
                }
            }
            catch (Exception ex)
            {
                WriteLog(ex.Message);

            }


        }

        private void fetchSecondLevelData(Combi c)
        {
            ArrayList ht = new ArrayList();
            Hashtable Qht = new Hashtable();
            DataTable table = MasterTable.Tables[c.Table];
            if (table==null || table.Rows.Count == 0) return;
            bool skip = false;
            
            
            string RefTable =c.FK;
            if (RefTable.ToUpper() == TargetTable.ToUpper()) return;
            string Orgstr = "SELECT top 1 * FROM " + RefTable + " (NOLOCK) WHERE " + CreateWhereCLause(RefTable, c.dr, out ht, out Qht);
            string str = string.Empty;

            int count = 0;
            foreach (DataRow dr1 in table.Rows)
            {
                str = Orgstr;
                foreach (string strcol in ht)
                {
                    if (ht.Count == 1 && dr1[strcol].ToString().Trim() == "")
                    {
                        skip = true;
                    }
                    else
                    {
                        if (dr1[strcol].ToString().Trim() == "" && skip)
                        {
                            skip = true;
                        }
                        else
                        {
                            skip = false;
                        }

                        str = str.Replace("$" + Qht[strcol].ToString() + "$", dr1[strcol].ToString().Trim());


                    }
                    count++;
                }
                if (skip) continue;
               
                WriteData(c.dr, str, false, RefTable);

            }

        }

		internal string CreateWhereCLause(string PrimaryTable,DataRow dr , out ArrayList ht ,out Hashtable QueryHT )
		{
                string pKey = "crefcol";
                string fKey = "ckeycol";
                string PrimaryKey = "";
                string refkey = "";
                bool AND = false;
                string sql = "";
                ArrayList internalHT = new ArrayList();
                Hashtable QHT = new Hashtable();

                for (int i = 1; i < 17; i++)
                {

                    if (!string.IsNullOrEmpty(dr[pKey + i].ToString().Trim()))
                    {
                        PrimaryKey = dr[pKey + i].ToString().Trim();
                        refkey = dr[fKey + i].ToString().Trim();
                        if (!AND)
                        {
                            sql += refkey + " = '$" + refkey + "$' ";
                            AND = true;
                            internalHT.Add(PrimaryKey);
                            QHT.Add(PrimaryKey,refkey);
                        }
                        else
                        {
                            sql += " and " + refkey + " = '$" + refkey + "$'";
                            internalHT.Add(PrimaryKey);
                            QHT.Add(PrimaryKey, refkey );
                        }
                    }


                }
                ht = internalHT;
                QueryHT = QHT;
                return sql;
            
		}

		internal string createclause(string PrimaryTable,string RefTable, DataRow dr)
		{
			string pKey = "crefcol";
			string fKey = "ckeycol";
			string PrimaryKey  = "";
			string refkey = "";
			bool AND = false;
			string sql = "";

            for (int i = 1; i < 17; i++)
            {

                if (!string.IsNullOrEmpty(dr[pKey + i].ToString().Trim()))
                {

                    PrimaryKey = dr[pKey + i].ToString();
                    refkey = dr[fKey + i].ToString();
                    if (!AND)
                    {
                        sql += PrimaryTable + "." + PrimaryKey + " = " + RefTable + "." + refkey + " ";
                        AND = true;
                    }
                    else
                    {
                        sql += " and " + PrimaryTable + "." + PrimaryKey + " = " + RefTable + "." + refkey + " ";
                    }
                }


            }
			return sql;
		}

		internal DataTable ExecuteQuery(string text,string tablename)
		{
			try
			{   if(CheckifQueryExists(text))return null;
                if(tablename != "ref") WriteLog(text);
				SqlCommand cmd = new SqlCommand(text,SourceConnection);
				DataSet results = new DataSet();
				SqlDataAdapter Adapter = new SqlDataAdapter(cmd);
				Adapter.Fill(results);
				results.Tables[0].TableName = tablename;
                return results.Tables[0].Copy();
			}
			catch(Exception ex)
			{
				throw( new Exception( ex.Message +" "+ text));
			}
			
			
		}
        internal DataTable ExecuteQueryonDest(string text, string tablename)
        {
            try
            {   
                
                SqlCommand cmd = new SqlCommand(text, DestinConnection);
                DataSet results = new DataSet();
                SqlDataAdapter Adapter = new SqlDataAdapter(cmd);
                Adapter.Fill(results);
                results.Tables[0].TableName = tablename;
                return results.Tables[0].Copy();
            }
            catch (Exception ex)
            {
                throw (new Exception(ex.Message + " " + text));
            }


        }

		
		Hashtable LockedTables = new Hashtable();
		internal void updateData(DataTable dt)
		{
            string CurrentTable = dt.TableName;
            string execp =string.Empty;
            try
            {
                WriteLog("Updating data for table " + dt.TableName);
                SqlCommand cmd = new SqlCommand("select * from " + dt.TableName + " where 1=0", DestinConnection);
                if (DestinConnection.State == ConnectionState.Closed) DestinConnection.Open();
                using (SqlDataAdapter Adapter = new SqlDataAdapter(cmd))
                {   
                    DataTable dtfill = new DataTable();
                    dtfill.TableName = dt.TableName;
                    Adapter.FillSchema(dtfill,SchemaType.Source);
                    dtfill = getwhereClause(dt, dtfill);
                    Adapter.ContinueUpdateOnError = true;
                    SqlCommandBuilder cmdbldr = new SqlCommandBuilder(Adapter);
                    Adapter.Update(dtfill);
                }
                MasterTable.Tables.Remove(dt.TableName);
                LockedTables.Remove(dt.TableName);
                
            }
            catch (SqlException ex)
            {
               
                if (ex.Number == 2627 || ex.Message.ToLower().Contains("duplicate")) // Primary duplicate
                {
                    MasterTable.Tables.Remove(dt.TableName);
                }
              
                else
                {   
                    if (MasterTable.Tables.Contains(CurrentTable))
                    {   
                        MasterTable.Tables.Remove(dt);
                        MasterTable.Tables.Add(dt);
                     
                        if (!LockedTables.Contains(dt.TableName))
                        {
                            LockedTables[CurrentTable] = 1;
                        }
                        else
                        {
                            LockedTables[dt.TableName] = (int)LockedTables[dt.TableName] + 1;
                        }
                    }
                    execp =ex.Message;

                }
            }
            catch (Exception ex)
            {   
                MasterTable.Tables.Remove(dt.TableName);
            }
            finally
            {
                if (LockedTables.Contains(CurrentTable))
                {
                    if ((int)LockedTables[CurrentTable] >= 5)
                    {
                        
                        WriteLog("Trying to resolve FKeys for table . " + CurrentTable);
                        Regex regex = new Regex(@"""\w+""", RegexOptions.IgnoreCase);
                        MatchCollection ErrorColl = regex.Matches(execp);
                        string FK = string.Empty;
                        if (ErrorColl.Count > 0)
                        {
                            FK = ErrorColl[0].Value.ToUpper().Replace("\"", "");

                            string Query = "sp_MStablerefs N'dbo." + CurrentTable + " ', N'actualtables', N'both', null ";
                            DataTable dsref = ReorderKeys(ExecuteQuery(Query, "ref"), CurrentTable);
                            DataRow[] drselect = dsref.Select("Constraint='" + FK + "'");
                            if(drselect.Length >0)
                            fetchSecondLevelData(getCombiData(drselect[0], CurrentTable));
                        MasterTable.Tables.Remove(dt);
                        MasterTable.Tables.Add(dt);
                        }
                    }
                    else if((int)LockedTables[CurrentTable] >= 7)
                    {

                        LockedTables.Remove(CurrentTable);
                        MasterTable.Tables.Remove(CurrentTable);
                        WriteLog("Failed while writing data for table " + CurrentTable + "\n" + execp);
                    }

                }
            }

		}

		
		internal bool TestConnection()
		{

            
			this.Text = "Data Sync Testing connection";	
			try
			{
                DestinConnection.Open();
                DestinConnection.Close();
                SourceConnection.Open();
                SourceConnection.Close();
				
			}
			catch(SqlException ex)
			{
                MessageBox.Show(ex.Message);
                return false;
			}
			
			
			this.Text = "Data Sync connection authenticated";
			return true;
			
		}

        internal void upload(IAsyncResult A)
		{   
            
            WriteLog("Data Sync uploading primary tables in progress");			
			DataSet PrimaryTable = new DataSet();
			
			SetProgressMax(MasterTable.Tables.Count);
            UpdateProgressbar(0);

            while (MasterTable.Tables.Count > 0)
			{	

				if(MasterTable.Tables[0].Rows.Count > 0)
				{
                    updateData(MasterTable.Tables[0]);
				}
				else
				{
                    MasterTable.Tables.RemoveAt(0);
				}
                PerformStep();
			}
            
            WriteLog("Data upload finished");
            settext("Data Sync");

            this.sqlConnection1.Close();
            
		}



     
        internal void WriteLog(string Log)
        {
            if (txtLog.InvokeRequired)
            {
                LogDelegate updateLable = delegate(string value) { txtLog.AppendText(value + Environment.NewLine); txtLog.ScrollToCaret(); txtLog.ScrollToCaret(); };
                txtLog.Invoke(updateLable,Log);
                
            }
            else
            {
                txtLog.AppendText(Log );
                txtLog.AppendText(Environment.NewLine);
                txtLog.ScrollToCaret();
            }
        }

        internal void UpdateProgressbar(int value)
        {
            if (progressBar1.InvokeRequired)
            {
                LogDelegate updateProgress = delegate(string step) { progressBar1.Value = Convert.ToInt16(step); };
                progressBar1.Invoke(updateProgress, value.ToString());
            }
            else
            {
                progressBar1.Value = Convert.ToInt16(value);
            }
        }

        internal void PerformStep()
        {
            oDelegate pbar = delegate() { progressBar1.PerformStep(); };
            progressBar1.Invoke(pbar);
        }

        internal void SetProgressMax(int Max)
        {
            if (progressBar1.InvokeRequired)
            {
                LogDelegate updateProgress = delegate(string value) { progressBar1.Maximum = Convert.ToInt16(value); };
                progressBar1.Invoke(updateProgress, Max.ToString());
            }
            else
            {
                progressBar1.Maximum = Convert.ToInt16(Max);
            }
        }

        internal void settext(string Log)
        {

            LogDelegate updateLable = delegate(string value) { this.Text = value; };
            this.Invoke(updateLable, Log);
           
        }

        internal struct Combi
        {
            public string PK;
            public string FK;
            public DataRow dr;
            public string Table;

        }

        Dictionary<string, Combi> AllKeys = new Dictionary<string, Combi>();

        private void AddKeys(DataTable dtref,string Table)
        {
            if (dtref == null) return;
            string Key = string.Empty;
            foreach (DataRow dr in dtref.Rows)
            {
                if (dr["pk_table"].ToString().Trim() == dr["fk_table"].ToString().Trim()) continue;
                Key = dr["pk_table"].ToString().Trim() + dr["fk_table"].ToString().Trim() + dr["cKeyCol1"].ToString().Trim();
                if (!AllKeys.ContainsKey(Key))
                {
                    Combi cmb = new Combi();
                    cmb.PK = dr["pk_table"].ToString().Trim();
                    cmb.FK = dr["fk_table"].ToString().Trim();
                    cmb.dr=dr;
                    cmb.Table=Table;

                    AllKeys.Add(Key, cmb);
                }
                      
            }
        }

        private Combi getCombiData(DataRow dr,string Table)
        {
            
            string Key = string.Empty;
            Combi cmb=new Combi();
            
            {
                cmb = new Combi();
                if (dr["pk_table"].ToString().Trim() != dr["fk_table"].ToString().Trim())
                {
                    Key = dr["pk_table"].ToString().Trim() + dr["fk_table"].ToString().Trim() + dr["cKeyCol1"].ToString().Trim();
                    if (!AllKeys.ContainsKey(Key))
                    {
                        cmb.PK = dr["pk_table"].ToString().Trim();
                        cmb.FK = dr["fk_table"].ToString().Trim();
                        cmb.dr = dr;
                        cmb.Table = Table;
                    }
                }
            }

            return cmb;
        }

        private DataTable ReorderKeys(DataTable dt, string TargetTable)
        {
            object ReferenceTable = null;
            string pKey = "crefcol";
            string fKey = "ckeycol";

            if (dt == null) return null;

            //re-order primary and ref tables
            foreach (DataRow dr in dt.Rows)
            {
                //if (dr["PK_Table"].ToString().Trim() == dr["FK_Table"].ToString().Trim())
                //{ dr.Delete(); continue; }
                if (dr["FK_Table"].ToString().Trim().ToUpper() == TargetTable.ToUpper())
                {
                    ReferenceTable = dr["PK_Table"];
                    dr["PK_Table"] = TargetTable;
                    dr["FK_Table"] = ReferenceTable;

                    for (int keys = 1; keys <= 16; keys++)
                    {
                        ReferenceTable = dr[fKey + keys];
                        dr[fKey + keys] = dr[pKey + keys];
                        dr[pKey + keys] = ReferenceTable;
                    }

                }
            }

            return dt;
        }

        private string CalculateMD5Hash(string input)
        {
            // step 1, calculate MD5 hash from input
            MD5 md5 = System.Security.Cryptography.MD5.Create();
            byte[] inputBytes = System.Text.Encoding.ASCII.GetBytes(input);
            byte[] hash = md5.ComputeHash(inputBytes);

            // step 2, convert byte array to hex string
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < hash.Length; i++)
            {
                sb.Append(hash[i].ToString("X2"));
            }
            return sb.ToString();
        }

        List<string> allQueries = new List<string>();
        private bool CheckifQueryExists(string Query)
        {
            
            string Hash = CalculateMD5Hash(Query);
            if (!allQueries.Contains(Hash))
            {
                allQueries.Add(Hash);
                return false;
            }
            return true;
        }

        private SqlConnection DestinConnection
        {
            get
            {
                return new SqlConnection("workstation id=IMPACT;packet size=4096;integrated security=SSPI;data source=" + txtServerDest.Text + ";persist security info=False;initial catalog=" + txtDataBaseDest.Text);
            }
        }
        private SqlConnection SourceConnection
        {
            get
            {
                return new SqlConnection("workstation id=IMPACT;packet size=4096;integrated security=SSPI;data source=" + txtServer.Text + ";persist security info=False;initial catalog=" + txtDataBase.Text);
            }
        }
        private DataTable getwhereClause(DataTable dtsource, DataTable dtdes)
        {
            DataColumn[] dclist = dtdes.PrimaryKey;
            foreach (DataRow dr in dtsource.Rows)
            {
                string SQLSTMT ="SELECT * FROM "+dtsource.TableName+" WHERE 1=1 ";
                foreach (DataColumn dc in dclist)
                {
                    SQLSTMT += " AND " + dc.ColumnName + " = '" + dr[dc.ColumnName] + "'";
                }
                dtdes.Merge(ExecuteQueryonDest(SQLSTMT, dtsource.TableName), false);
            }
            foreach (DataRow dr in dtsource.Rows)
                dtdes.LoadDataRow(dr.ItemArray, false);

            return dtdes;
        }

       
	}


}

